﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services.Protocols;
using log4net;
namespace Server_NhaSach.DataAccess.HelperLayer
{
    public class Helper
    {
        #region "Khai báo các biến"
        private SqlConnection _cnn;
        private SqlDataAdapter adap;
        private string _error;
        private ILog logger;
        #endregion

        #region "Khai báo các thuộc tính"
        public string Error
        {
            get { return _error; }
        }
        #endregion

        #region "Khai báo các Method"

        public Helper()
        {
            string chuoi_ket_noi = @"Data Source=.;Initial Catalog=QLNhaSach;Integrated Security=True";
            _cnn = new SqlConnection(chuoi_ket_noi);
            adap = new SqlDataAdapter();
            logger = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        }

        public Helper(string Chuoi_Retore)
        {
            string chuoi_ket_noi = @"Data Source=.;Initial Catalog="+Chuoi_Retore+";Integrated Security=True";
            _cnn = new SqlConnection(chuoi_ket_noi);
            adap = new SqlDataAdapter();
            logger = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        }

        private SqlConnection openCnn()
        {
            if (_cnn.State == ConnectionState.Closed || _cnn.State == ConnectionState.Broken)
            {
                _cnn.Open();
            }
            return _cnn;
        }

        private SqlConnection closeCnn()
        {
            if (_cnn.State == ConnectionState.Open)
                _cnn.Close();
            return _cnn;
        }

        // select query by store procedure//
        /// <method>
        /// Select Query by store procedure
        /// </method>
        public DataSet SelectQuery(String sql, params SqlParameter[] sqlParameter)
        {
            SqlCommand myCommand = new SqlCommand();
            DataSet ds = new DataSet();
            try
            {
                myCommand.Connection = openCnn();
                myCommand.CommandText = sql;
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Clear();
                myCommand.Parameters.AddRange(sqlParameter);
                myCommand.ExecuteNonQuery();
                adap.SelectCommand = myCommand;
                adap.Fill(ds);
            }
            catch (Exception ex)
            {
                _error = ex.Message;
                logger.Error("Loi:" + ex.Message);
                throw ex;
            }
            finally
            {
                closeCnn();
            }
            return ds;
        }

        //select query by text//
        /// <method>
        /// Select Query by text
        /// </method>
        public DataSet SelectQueryByText(String sql, params SqlParameter[] sqlParameter)
        {
            SqlCommand myCommand = new SqlCommand();
            DataSet ds = new DataSet();
            try
            {
                myCommand.Connection = openCnn();
                myCommand.CommandText = sql;
                myCommand.CommandType = CommandType.Text;
                myCommand.Parameters.Clear();
                myCommand.Parameters.AddRange(sqlParameter);
                myCommand.ExecuteNonQuery();
                adap.SelectCommand = myCommand;
                adap.Fill(ds);
            }
            catch (Exception ex)
            {
                _error = ex.Message;
                logger.Error("Loi:" + ex.Message);
            }
            finally
            {
                closeCnn();
            }
            return ds;
        }

        /// <method>
        /// Insert Query
        /// </method>
        public bool InsertQuery(String sql, SqlParameter[] sqlParameter)
        {
            bool kiemtra = false;
            SqlCommand myCommand = new SqlCommand();
            try
            {
                myCommand.Connection = openCnn();
                myCommand.CommandText = sql;
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Clear();
                myCommand.Parameters.AddRange(sqlParameter);
                adap.InsertCommand = myCommand;
                myCommand.ExecuteNonQuery();
                kiemtra = true;
            }
            catch (Exception ex)
            {
                _error = ex.Message;
                logger.Error("Loi Insert: " + ex.Message);
                throw new SoapException("Lỗi kết nối: " + _error, new System.Xml.XmlQualifiedName("CSDL"));
            }
            finally
            {                
                closeCnn();                                
            }
            return kiemtra;
        }
        /// <method>
        /// Update Query
        /// </method>
        public bool UpdateQuery(String sql, SqlParameter[] sqlParameter)
        {
            bool kiemtra = false;
            SqlCommand myCommand = new SqlCommand();
            try
            {
                myCommand.Connection = openCnn();
                myCommand.CommandText = sql;
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Clear();
                myCommand.Parameters.AddRange(sqlParameter);
                adap.UpdateCommand = myCommand;
                myCommand.ExecuteNonQuery();
                kiemtra = true;
            }
            catch (Exception ex)
            {
                _error = ex.Message;
                logger.Error("Loi Update: " + ex.Message);
                throw new SoapException("Lỗi kết nối: " + _error, new System.Xml.XmlQualifiedName("CSDL"));                
            }
            finally
            {                
                closeCnn();                
            }
            return kiemtra;
        }
        /// <method>
        /// Delete query
        /// </method>

        public bool DeleteQuery(String sql, SqlParameter[] sqlParameter)
        {
            bool kiemtra = false;
            SqlCommand myCommand = new SqlCommand();
            try
            {
                myCommand.Connection = openCnn();
                myCommand.CommandText = sql;
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Clear();
                myCommand.Parameters.AddRange(sqlParameter);
                adap.DeleteCommand = myCommand;
                myCommand.ExecuteNonQuery();
                kiemtra = true;
            }
            catch (Exception ex)
            {
                _error = ex.Message;
                logger.Error("Loi Delete: " + ex.Message);                
                throw new SoapException("Lỗi kết nối: " + _error, new System.Xml.XmlQualifiedName("CSDL"));
            }
            finally
            {
                closeCnn();                
            }
            return kiemtra;
        }        

        /// <summary>
        /// Hàm lấy dữ liệu có kèm điều kiện hoặc không điều kiện
        /// Phân trang theo vị trí và số dòng
        /// </summary>
        /// <param name="cautruyvan">Tên Store Procedure</param>
        /// <param name="vitri">Vị trí bắt đầu để lấy dữ liệu</param>
        /// <param name="sodong">Số dòng muốn lấy</param>
        /// <param name="pars">Mảng tham số truyền vào</param>
        /// <returns>Trả ra một DataTable</returns>
        public DataTable ExcuteLoadpaging(string cautruyvan, int vitri, int sodong, params SqlParameter[] pars)
        {
            DataTable tbl = new DataTable();
            using (SqlDataAdapter da = new SqlDataAdapter())
            {
                SqlCommand cmd = new SqlCommand(cautruyvan, _cnn) { CommandType = CommandType.StoredProcedure };
                da.SelectCommand = cmd;
                da.Fill(vitri, sodong, tbl);
            }
            return tbl;
        }

        /// <summary>
        /// Hàm dùng để lấy tất cả các trường theo một bảng nào đó
        /// </summary>
        /// <param name="tbl">một DataTable</param>
        /// <param name="SelectQuery">Tên Store Procedure</param>
        public void LoadSchema(DataTable tbl, string SelectQuery)
        {
            using (SqlDataAdapter da = new SqlDataAdapter())
            {
                SqlCommand cmd = new SqlCommand(SelectQuery, _cnn);
                da.SelectCommand = cmd;
                try
                {
                    openCnn();
                    da.FillSchema(tbl, SchemaType.Source);
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    closeCnn();
                }
            }
        }
        #endregion
    }
}